package com.yzk.auction.dao;

import com.yzk.auction.entity.FirstCategory;
import com.yzk.auction.entity.GoodsDTO;
import com.yzk.auction.entity.SecondCategory;
import com.yzk.auction.utils.JDBCUtils;
import com.yzk.auction.utils.PageModel;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

/**
 * @author： lys
 * @date： 2020/8/21 20:43
 * @类名： GoodsDao
 * @Title：
 */

public class GoodsDao {
    private DataSource dataSource = JDBCUtils.getDataSource();
    private GoodsDTO goodsDTO = null;

    /**
     * 查询油画数据
     * @param
     * @param
     * @return GoodsDTO商品详情对象
     * */
    public static List<GoodsDTO> query() {

        DataSource dataSource = JDBCUtils.getDataSource();
        QueryRunner qr = new QueryRunner(dataSource);
        String sql = "select goods_id, goods_name, g.second_cid as second_cid, end_time, low_price, auction_times, start_time, end_price, goods_picture, goods_desc, price_markup, appraisal_price, second_cname, s.first_cid as first_cid, first_cname from tb_goods g,tb_second_category s,tb_first_category f where g.second_cid = s.second_cid and s.first_cid = f.first_cid";
        List<GoodsDTO> list = null;
        try {
            list = qr.query(sql,new BeanListHandler<>(GoodsDTO.class),new Object[]{});

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return list;
    }

    /**
     * 分页查询油画数据
     * @param page 页号
     * @param rows 每页记录数
     * @return PageModel分页对象
     * */
    public PageModel pagination(int page, int rows) {
        QueryRunner qr = new QueryRunner(dataSource);
        String sql = "select goods_id, goods_name, g.second_cid as second_cid, end_time, low_price, auction_times, start_time, end_price, goods_picture, goods_desc, price_markup, appraisal_price, second_cname, s.first_cid as first_cid, first_cname from tb_goods g,tb_second_category s,tb_first_category f where g.second_cid = s.second_cid and s.first_cid = f.first_cid";
        PageModel pageModel = null;
        try {
            List<GoodsDTO> list = qr.query(sql,new BeanListHandler<>(GoodsDTO.class),new Object[]{});
            //PageModel分页处理得到分页数据及分页附加
            pageModel = new PageModel(list,page,rows);

            System.out.println(pageModel);

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return pageModel;
    }
    /**
     * 首页模糊查询
     * @param like 索引
     * @param page 页号
     * @param rows 每页记录数
     * @return PageModel分页对象
     */
    public PageModel paginationLike(String like, int page, int rows){
        QueryRunner queryRunner = new QueryRunner(dataSource);
        PageModel pageModel = null;
        try {
            List<GoodsDTO> likeList = queryRunner.query("SELECT * FROM tb_goods WHERE goods_name LIKE ?", new BeanListHandler<GoodsDTO>(GoodsDTO.class), "%" + like + "%");
            pageModel = new PageModel(likeList, page, rows);
            return pageModel;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            return null;
        }
    }

    /**
     * 根据二级分类名查询数据，并分页显示
     * @param second_cid 一级分类id
     * @param page 页号
     * @param rows 每页记录数
     * @return PageModel分页对象
     */
    public PageModel paginationsecondid(String second_cid,int page, int rows) {
        QueryRunner runner = new QueryRunner(JDBCUtils.getDataSource());
        String sql = "select goods_id, goods_name, g.second_cid as second_cid, end_time, low_price, auction_times, start_time, end_price, goods_picture, goods_desc, price_markup, appraisal_price, second_cname, s.first_cid as first_cid, first_cname from tb_goods g,tb_second_category s,tb_first_category f where g.second_cid = s.second_cid and s.first_cid = f.first_cid and s.second_cname=?;";
        PageModel pageModel = null;
        try {
            List<GoodsDTO> list = runner.query(sql,new BeanListHandler<GoodsDTO>(GoodsDTO.class),second_cid);
            pageModel = new PageModel(list,page,rows);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return pageModel;
    }

    /**
     * 根据Id删除商品信息
     * @param id
     */
    public void delete(Integer id){
        Connection conn = null;
        try {
            conn = JDBCUtils.getConnection();
            QueryRunner qr = new QueryRunner();
            conn.setAutoCommit(false);
            String sql = "delete from tb_goods where goods_id = ?";
            qr.update(conn,sql,new Object[]{id});
            conn.commit();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            try {
                if(conn!=null && !conn.isClosed()){
                    conn.rollback();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 查询所有一级分类
     * @return 一级分类的list集合
     */
    public List<FirstCategory> findAllFirstCategory(){
        QueryRunner qr = new QueryRunner(dataSource);
        String sql = "select * from tb_first_category";
        List<FirstCategory> list = null;
        try {
            list = qr.query(sql,new BeanListHandler<>(FirstCategory.class),new Object[]{});
            for (FirstCategory f:list){
                System.out.println(f);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return list;
    }

    /**
     * 查询所有二级分类
     * @return 二级分类的list集合
     */
    public List<SecondCategory> findAllSecondCategory(){
        QueryRunner qr = new QueryRunner(dataSource);
        String sql = "select * from tb_second_category";
        List<SecondCategory> list = null;
        try {
            list = qr.query(sql,new BeanListHandler<>(SecondCategory.class),new Object[]{});
            for (SecondCategory s:list){
                System.out.println(s);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return list;
    }

}
